const Router = require("@koa/router");
const { Pool } = require("pg");

const path = require('path');
require('dotenv').config({ path: path.resolve(__dirname, '../.env') });

// 配置数据库连接池
const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

const router = new Router({ prefix: "/notes" });

/*
CREATE TABLE notes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  domain TEXT NOT NULL,
  url TEXT NOT NULL,
  is_public BOOLEAN DEFAULT FALSE,
  updated_at TIMESTAMP DEFAULT NOW()
);
*/

/**
 * 内部函数：创建笔记逻辑
 */
async function _createNote({ user_id, title, content, domain, url, is_public = false }) {
  if (!user_id || !title || !content || !domain || !url) {
    const missingFields = [];
    if (!user_id) missingFields.push('user_id');
    if (!title) missingFields.push('title');
    if (!content) missingFields.push('content');
    if (!domain) missingFields.push('domain');
    if (!url) missingFields.push('url');
    throw new Error(`所有必填字段均不能为空: ${missingFields.join(', ')}`);
  }

  // 处理 URL 锚点
  if (url.includes('#')) {
    url = url.split('#')[0];
  }

  // 处理 bilibili.com 的特殊 URL
  if (url.includes('bilibili.com') && url.includes('/?spm_id_from')) {
    url = url.replace('/?spm_id_from', '?spm_id_from');
  }

  const checkSql = 'SELECT COUNT(*) FROM notes WHERE user_id = $1 AND url = $2';
  const { rows: countRows } = await pool.query(checkSql, [user_id, url]);
  if (parseInt(countRows[0].count, 10) > 0) {
    throw new Error('用户已为此URL创建过笔记，每个URL最多只能创建一篇笔记');
  }

  const sql = `
    INSERT INTO notes (user_id, title, content, domain, url, is_public)
    VALUES ($1, $2, $3, $4, $5, $6)
    RETURNING *;
  `;
  const params = [user_id, title, content, domain, url, is_public];
  const { rows } = await pool.query(sql, params);
  return rows[0];
}

/**
 * 内部函数：根据ID删除笔记逻辑
 */
async function _deleteNote(id, user_id) {
  if (!id || !user_id) {
    throw new Error('笔记ID和用户ID不能为空');
  }
  const sql = 'DELETE FROM notes WHERE id = $1 AND user_id = $2 RETURNING id;';
  const { rows, rowCount } = await pool.query(sql, [id, user_id]);
  if (rowCount === 0) {
    throw new Error('笔记未找到或用户无权限删除');
  }
  return { id: rows[0].id, deleted: true };
}

/**
 * 内部函数：根据笔记ID和用户ID查询笔记
 */
async function _getNoteByIdAndUserId(id, user_id) {
  const sql = 'SELECT * FROM notes WHERE id = $1 AND user_id = $2;';
  const { rows } = await pool.query(sql, [id, user_id]);
  return rows.length > 0 ? rows[0] : null;
}

/**
 * 内部函数：修改笔记逻辑
 */
async function _updateNote(id, user_id, updates) {
  if (!id || !user_id) {
    throw new Error('笔记ID和用户ID不能为空');
  }
  if (!updates || Object.keys(updates).length === 0) {
    const existingNote = await _getNoteByIdAndUserId(id, user_id);
    if (!existingNote) throw new Error('笔记未找到或用户无权限');
    return existingNote;
  }
  if (updates.url || updates.domain) {
    throw new Error('禁止修改url和domain');
  }

  const setClauses = [];
  const params = [];
  let paramIndex = 1;

  if (updates.title !== undefined) {
    setClauses.push(`title = $${paramIndex++}`);
    params.push(updates.title);
  }
  if (updates.content !== undefined) {
    setClauses.push(`content = $${paramIndex++}`);
    params.push(updates.content);
  }
  if (updates.is_public !== undefined) {
    setClauses.push(`is_public = $${paramIndex++}`);
    params.push(updates.is_public);
  }

  if (setClauses.length === 0) {
    const existingNote = await _getNoteByIdAndUserId(id, user_id);
    if (!existingNote) throw new Error('笔记未找到或用户无权限');
    return existingNote;
  }

  setClauses.push(`updated_at = NOW()`);
  params.push(id);
  params.push(user_id);

  const sql = `
    UPDATE notes
    SET ${setClauses.join(', ')}
    WHERE id = $${paramIndex++} AND user_id = $${paramIndex++}
    RETURNING *;
  `;
  const { rows, rowCount } = await pool.query(sql, params);
  if (rowCount === 0) {
    const noteExists = await _getNoteByIdAndUserId(id, user_id);
    if (!noteExists) {
      throw new Error('笔记未找到');
    } else {
      throw new Error('用户无权限修改此笔记，或者没有实际内容更新');
    }
  }
  return rows[0];
}

/**
 * 内部函数：根据URL和用户ID查询笔记逻辑
 */
async function _getNotesByUrlAndUserId(url, user_id) {
  if (!url || !user_id) {
    throw new Error('URL和用户ID不能为空');
  }

  // 处理 URL 锚点
  if (url.includes('#')) {
    url = url.split('#')[0];
  }

  // 处理 bilibili.com 的特殊 URL
  if (url.includes('bilibili.com') && url.includes('/?spm_id_from')) {
    url = url.replace('/?spm_id_from', '?spm_id_from');
  }

  const sql = 'SELECT * FROM notes WHERE url = $1 AND user_id = $2;';
  const { rows } = await pool.query(sql, [url, user_id]);
  return rows;
}

/**
 * 内部函数：查询某个用户的全部笔记逻辑（支持分页和关键词搜索）
 */
async function _getNotesByUserId(user_id, { page = 1, pageSize = 10, keyword = '' } = {}) {
  if (!user_id) {
    throw new Error('用户ID不能为空');
  }
  const validPage = Math.max(1, parseInt(page, 10) || 1);
  const validPageSize = Math.max(1, parseInt(pageSize, 10) || 10);
  const offset = (validPage - 1) * validPageSize;

  let whereClause = 'WHERE user_id = $1';
  const queryParams = [user_id];
  
  // 添加关键词搜索条件
  if (keyword.trim()) {
    const searchPattern = `%${keyword.trim()}%`;
    whereClause += ' AND (title ILIKE $2 OR content ILIKE $2 OR domain ILIKE $2)';
    queryParams.push(searchPattern);
  }

  const notesSql = `
    SELECT * FROM notes 
    ${whereClause} 
    ORDER BY created_at DESC 
    LIMIT $${queryParams.length + 1} 
    OFFSET $${queryParams.length + 2};
  `;
  const countSql = `SELECT COUNT(*) FROM notes ${whereClause};`;

  queryParams.push(validPageSize, offset);

  const { rows: notes } = await pool.query(notesSql, queryParams);
  const { rows: countResult } = await pool.query(countSql, keyword.trim() ? queryParams.slice(0, 2) : queryParams.slice(0, 1));

  const totalNotes = parseInt(countResult[0].count, 10);
  const totalPages = Math.ceil(totalNotes / validPageSize);

  return {
    notes,
    currentPage: validPage,
    pageSize: validPageSize,
    totalNotes,
    totalPages,
    keyword: keyword.trim() || null
  };
}

// --- 路由定义 ---

// 创建笔记
router.post("/", async (ctx) => {
  try {
    // 假设 user_id 从认证中间件或者请求体中获取
    // 为了示例，我们从请求体中获取 user_id
    const { user_id, title, content, domain, url, is_public } = ctx.request.body;
    if (!user_id) {
      ctx.status = 400;
      ctx.body = { success: false, message: "user_id 是必需的" };
      return;
    }
    const note = await _createNote({ user_id, title, content, domain, url, is_public });
    ctx.status = 201;
    ctx.body = { success: true, data: note };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 创建笔记路由失败:`, err);
    ctx.status = err.message.includes('每个URL最多只能创建一篇笔记') || err.message.includes('所有必填字段均不能为空') ? 400 : 500;
    ctx.body = { success: false, message: err.message };
  }
});

// 根据ID删除笔记
router.delete("/:id", async (ctx) => {
  try {
    const { id } = ctx.params;
    // 假设 user_id 从认证中间件获取，这里为了简单，我们从查询参数或请求体获取
    // 例如: const user_id = ctx.state.user.id; (如果用了认证中间件)
    // 或者: const { user_id } = ctx.request.query; (不推荐用于生产)
    const user_id = parseInt(ctx.request.query.user_id, 10); // 示例：从查询参数获取
    if (!user_id) {
      ctx.status = 400;
      ctx.body = { success: false, message: "user_id 查询参数是必需的" };
      return;
    }
    const result = await _deleteNote(id, user_id);
    ctx.body = { success: true, data: result };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 删除笔记路由失败:`, err);
    ctx.status = err.message.includes('笔记未找到或用户无权限删除') ? 404 : 500;
    ctx.body = { success: false, message: err.message };
  }
});

// 修改笔记
router.put("/:id", async (ctx) => {
  try {
    const { id } = ctx.params;
    const updates = ctx.request.body;
    // 假设 user_id 从认证中间件获取
    const user_id = parseInt(updates.user_id, 10); // 示例：从请求体获取 user_id
    if (!user_id) {
      ctx.status = 400;
      ctx.body = { success: false, message: "user_id 是必需的" };
      return;
    }
    // 从updates中移除user_id，因为它不是notes表的字段
    const { user_id: _, ...noteUpdates } = updates;


    if (Object.keys(noteUpdates).length === 0) {
      ctx.status = 400;
      ctx.body = { success: false, message: "没有提供要更新的字段" };
      return;
    }

    const note = await _updateNote(id, user_id, noteUpdates);
    ctx.body = { success: true, data: note };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 修改笔记路由失败:`, err);
    ctx.status = err.message.includes('禁止修改') || err.message.includes('笔记未找到') ? 400 : 500;
    if (err.message.includes('用户无权限')) ctx.status = 403;
    ctx.body = { success: false, message: err.message };
  }
});

// 根据URL和用户ID查询用户的笔记
router.get("/url", async (ctx) => {
  try {
    const { url, user_id } = ctx.request.query;
    if (!url || !user_id) {
      ctx.status = 400;
      ctx.body = { success: false, message: "url 和 user_id 查询参数是必需的" };
      return;
    }
    const notes = await _getNotesByUrlAndUserId(url, parseInt(user_id, 10));
    ctx.body = { success: true, data: notes };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 按URL查询笔记路由失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

// 查询某个用户的全部笔记（支持分页和关键词搜索）
router.get("/user/:user_id", async (ctx) => {
  try {
    const user_id = parseInt(ctx.params.user_id, 10);
    const { page, pageSize, keyword } = ctx.request.query;
    if (isNaN(user_id)) {
      ctx.status = 400;
      ctx.body = { success: false, message: "无效的用户ID" };
      return;
    }
    const result = await _getNotesByUserId(user_id, {
      page: page ? parseInt(page, 10) : undefined,
      pageSize: pageSize ? parseInt(pageSize, 10) : undefined,
      keyword: keyword || ''
    });
    ctx.body = { success: true, data: result };
  } catch (err) {
    console.error(`${new Date().toLocaleString()} 查询用户全部笔记路由失败:`, err);
    ctx.status = 500;
    ctx.body = { success: false, message: err.message };
  }
});

module.exports = router;
